﻿#region
/**
 * $Date: 0001/1/1
*/
using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using BuXiaChu.OrderDishes.DAL.DataAccess;
using BuXiaChu.OrderDishes.Model;
#endregion

namespace BuXiaChu.OrderDishes.DAL
{
    public class RestaurantDAL
    {
        #region private const
        private const string PARAM_ID = "@Id";
        private const string PARAM_NAME = "@Name";
        private const string PARAM_CATEGORY = "@Category";
        private const string PARAM_ADDRESS = "@Address";
        private const string PARAM_TELEPHONE = "@Telephone";
        private const string PARAM_CITY_ID = "@CityId";
        private const string PARAM_AREA_ID = "@AreaId";
        private const string PARAM_CONTACT1 = "@Contact1";
        private const string PARAM_PHONE1 = "@Phone1";
        private const string PARAM_CONTACT2 = "@Contact2";
        private const string PARAM_PHONE2 = "@Phone2";
        private const string PARAM_CREATE_DATE = "@CreateDate";
        private const string PARAM_UPDATE_DATE = "@UpdateDate";
        private const string PARAM_WEBSITE = "@Website";
        private const string PARAM_REMARKS = "@Remarks";
        private static readonly string ConnectionString = SQLHelper.CONNECTION_STRING;
        #endregion

        private static void FillModelFrom(SqlDataReader reader, RestaurantModel obj)
        {
            #region
            if (reader != null && !reader.IsClosed)
            {
                obj.Id = reader.IsDBNull(reader.GetOrdinal("Id")) ? 0 : reader.GetInt32(reader.GetOrdinal("Id"));
                obj.Name = reader.IsDBNull(reader.GetOrdinal("Name")) ? String.Empty : reader.GetString(reader.GetOrdinal("Name"));
                obj.Category = reader.IsDBNull(reader.GetOrdinal("Category")) ? 0 : reader.GetInt32(reader.GetOrdinal("Category"));
                obj.Address = reader.IsDBNull(reader.GetOrdinal("Address")) ? String.Empty : reader.GetString(reader.GetOrdinal("Address"));
                obj.Telephone = reader.IsDBNull(reader.GetOrdinal("Telephone")) ? String.Empty : reader.GetString(reader.GetOrdinal("Telephone"));
                obj.CityId = reader.IsDBNull(reader.GetOrdinal("CityId")) ? 0 : reader.GetInt32(reader.GetOrdinal("CityId"));
                obj.AreaId = reader.IsDBNull(reader.GetOrdinal("AreaId")) ? 0 : reader.GetInt32(reader.GetOrdinal("AreaId"));
                obj.Contact1 = reader.IsDBNull(reader.GetOrdinal("Contact1")) ? String.Empty : reader.GetString(reader.GetOrdinal("Contact1"));
                obj.Phone1 = reader.IsDBNull(reader.GetOrdinal("Phone1")) ? String.Empty : reader.GetString(reader.GetOrdinal("Phone1"));
                obj.Contact2 = reader.IsDBNull(reader.GetOrdinal("Contact2")) ? String.Empty : reader.GetString(reader.GetOrdinal("Contact2"));
                obj.Phone2 = reader.IsDBNull(reader.GetOrdinal("Phone2")) ? String.Empty : reader.GetString(reader.GetOrdinal("Phone2"));
                obj.CreateDate = reader.IsDBNull(reader.GetOrdinal("CreateDate")) ? DateTime.MinValue : reader.GetDateTime(reader.GetOrdinal("CreateDate"));
                obj.UpdateDate = reader.IsDBNull(reader.GetOrdinal("UpdateDate")) ? DateTime.MinValue : reader.GetDateTime(reader.GetOrdinal("UpdateDate"));
                obj.Website = reader.IsDBNull(reader.GetOrdinal("Website")) ? String.Empty : reader.GetString(reader.GetOrdinal("Website"));
                obj.Remarks = reader.IsDBNull(reader.GetOrdinal("Remarks")) ? String.Empty : reader.GetString(reader.GetOrdinal("Remarks"));
            }
            #endregion
        }

        private static SqlParameter[] GetRestaurantParams(RestaurantModel obj)
        {
            #region
            SqlParameter[] dbParams ={					
					 SQLHelper.MakeParam(PARAM_ID, SqlDbType.Int,0,obj.Id),
					 SQLHelper.MakeParam(PARAM_NAME, SqlDbType.NVarChar, 50,obj.Name),					
					 SQLHelper.MakeParam(PARAM_CATEGORY, SqlDbType.Int,0,obj.Category),
					 SQLHelper.MakeParam(PARAM_ADDRESS, SqlDbType.NVarChar, 50,obj.Address),					
					 SQLHelper.MakeParam(PARAM_TELEPHONE, SqlDbType.NVarChar, 15,obj.Telephone),					
					 SQLHelper.MakeParam(PARAM_CITY_ID, SqlDbType.Int,0,obj.CityId),
					 SQLHelper.MakeParam(PARAM_AREA_ID, SqlDbType.Int,0,obj.AreaId),
					 SQLHelper.MakeParam(PARAM_CONTACT1, SqlDbType.NVarChar, 20,obj.Contact1),					
					 SQLHelper.MakeParam(PARAM_PHONE1, SqlDbType.NVarChar, 15,obj.Phone1),					
					 SQLHelper.MakeParam(PARAM_CONTACT2, SqlDbType.NVarChar, 20,obj.Contact2),					
					 SQLHelper.MakeParam(PARAM_PHONE2, SqlDbType.NVarChar, 15,obj.Phone2),
					 SQLHelper.MakeParam(PARAM_WEBSITE, SqlDbType.VarChar, 100,obj.Website),
                     SQLHelper.MakeParam(PARAM_REMARKS, SqlDbType.NVarChar, 1024,obj.Remarks)					
			};

            return dbParams;
            #endregion
        }

        public static bool Save(RestaurantModel obj)
        {
            #region
            try
            {
                SqlParameter[] dbParams = GetRestaurantParams(obj);

                if (obj.Id == 0)
                {
                    obj.Id = Convert.ToInt32(SQLHelper.ExecuteScalar(ConnectionString, CommandType.StoredProcedure, "Restaurant_InsertUpdate", dbParams));
                    return obj.Id > 0 ? true : false;
                }
                else
                {
                    var affectNum = SQLHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "Restaurant_InsertUpdate", dbParams);
                    return affectNum > 0 ? true : false;
                }
            }
            catch (Exception e)
            {
                Exception ex = new Exception("Restaurant-->InsertOrUpdate-->" + e.Message);

                ExceptionMessageDAL.Record(ex);
            }

            return false;
            #endregion
        }

        public static RestaurantModel GetRestaurantById(int id)
        {
            #region
            SqlDataReader reader = null;
            RestaurantModel obj = new RestaurantModel();

            try
            {
                SqlParameter[] dbParams = {
                        SQLHelper.MakeParam(PARAM_ID, SqlDbType.Int, 0, id)
                    };

                reader = SQLHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Restaurant_GetByRestaurantId", dbParams);

                if (reader.Read())
                {
                    FillModelFrom(reader, obj);
                }
            }
            catch (Exception e)
            {
                Exception ex = new Exception("Restaurant-->GetSingleObject-->" + e.Message);

                ExceptionMessageDAL.Record(ex);
            }
            finally
            {
                if (reader != null && !reader.IsClosed)
                    reader.Close();
            }

            return obj;
            #endregion
        }

        public static List<RestaurantModel> Search(string sqlWhere, int pageIndex, int pageSize, out int pageCount)
        {
            #region
            List<RestaurantModel> result = new List<RestaurantModel>();
            pageCount = 0;
            try
            {
                if (string.IsNullOrEmpty(sqlWhere))
                {
                    sqlWhere = " 1=1 ";
                }

                SqlParameter[] dbParams =
                {
                    SQLHelper.MakeParam("@sqlWhere", SqlDbType.NVarChar,0,sqlWhere),
                    SQLHelper.MakeParam("@pageIndex",SqlDbType.Int,0,pageIndex),
                    SQLHelper.MakeParam("@pageSize",SqlDbType.Int,0,pageSize),
                    SQLHelper.MakeOutParam("@pageCount", SqlDbType.Int,0,0)                    
                };
                using (SqlDataReader reader = SQLHelper.ExecuteReader(ConnectionString, CommandType.StoredProcedure, "Restaurant_Search", dbParams))
                {
                    while (reader.Read())
                    {
                        RestaurantModel model = new RestaurantModel();
                        FillModelFrom(reader, model);
                        result.Add(model);
                    }
                }
                pageCount = int.Parse(dbParams[3].Value.ToString());
            }
            catch (Exception e)
            {
                Exception ex = new Exception("Restaurant-->Search-->" + e.ToString());
                ExceptionMessageDAL.Record(ex);
            }
            return result;
            #endregion
        }
    }
}
